package com.edu.app.mapper.ai;

import com.edu.app.bean.virtualbean.customize.PoStudentModelStatus;
import com.edu.app.bean.virtualbean.customize.StudentModelStatus;
import com.edu.app.util.AppBaseMapper;
import com.edu.app.bean.core.SysUser;
import com.edu.app.model.customer.PendingActivityModel;
import com.edu.app.model.customer.RenewContractModel;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface SysAITeachersMapper extends AppBaseMapper<SysUser> {
    @Select("select distinct t1.id,t1.name as student,t5.value as lesson,t3.nickname as teacher,t4.points,t4.start_date,t4.end_date from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_customer_lesson t4 on t4.customer_id = t1.id " +
            "inner join s_list_of_value t5 on t5.id = t4.lesson_id " +
            "where t2.roleplay='teacher' and t1.status='1' and t4.points<='20' and t3.username=#{username} order by points")
    List<RenewContractModel> getRenewContractModelListByUser(@Param("username") String username);

    @Select("select distinct t1.id,t1.name as student,t5.value as lesson,t3.nickname as teacher,t4.points,t4.start_date,t4.end_date from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_customer_lesson t4 on t4.customer_id = t1.id " +
            "inner join s_list_of_value t5 on t5.id = t4.lesson_id " +
            "where t2.roleplay='teacher' and t1.status='1' and t3.username=#{username} order by points")
    List<RenewContractModel> getRenewContractModelListAllByUser(@Param("username") String username);

    @Select("select distinct t1.id,t1.name as student,t5.value as lesson,substring_index(GROUP_CONCAT(t3.nickname SEPARATOR '|'),'|',1) as teacher,t4.points,t4.start_date,t4.end_date from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_customer_lesson t4 on t4.customer_id = t1.id " +
            "inner join s_list_of_value t5 on t5.id = t4.lesson_id " +
            "where t2.roleplay='teacher' and t1.status='1' and t4.points<='20' group by t1.id,t1.name,t5.value,t4.points,t4.start_date,t4.end_date order by points")
    List<RenewContractModel> getRenewContractModelList();

    @Select("select distinct t1.id,t1.name as student,t5.value as lesson,substring_index(GROUP_CONCAT(t3.nickname SEPARATOR '|'),'|',1) as teacher,t4.points,t4.start_date,t4.end_date from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_customer_lesson t4 on t4.customer_id = t1.id " +
            "inner join s_list_of_value t5 on t5.id = t4.lesson_id " +
            "where t2.roleplay='teacher' and t1.status='1' and t5.value != '右脑试听课' group by t1.id,t1.name,t5.value,t4.points,t4.start_date,t4.end_date order by points")
    List<RenewContractModel> getRenewContractModelListAll();

    @Select("SELECT distinct t6.id,t6.name as student,t1.nickname as teacher,t3.time,t7.value as lesson,t3.status FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN S_LIST_OF_VALUE t4 on t4.id = t3.type_id " +
            "inner join s_customer_activity t5 on t3.id = t5.activity_id " +
            "inner join s_customer t6 on t6.id = t5.customer_id " +
            "inner join s_list_of_value t7 on t3.type_id = t7.id " +
            "WHERE t1.status = 1 and t4.type like '%S_LOV_LESSON%' and (t3.status = 0 or t3.status = 1) and t3.time < now() order by t1.nickname,t3.time desc")
    List<PendingActivityModel> getPendingActivityModelList();

    @Select("select id, name, class_name, class_points, " +
            "substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time," +
            "substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments from " +
            "(select distinct t1.id, t1.name, t5.value as class_name,t4.points as class_points,t6.create_time as time, t6.content as comments from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "left join s_customer_lesson t4 on t4.customer_id = t1.id " +
            "left join s_list_of_value t5 on t4.lesson_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "where t1.status = ${status} and t2.roleplay = 'teacher' and t3.username = #{username} order by t6.create_time desc) tb " +
            "group by id, name, class_name, class_points order by ${orderBy} ${sequence}")
    List<StudentModelStatus> getMyStudentsModel(@Param("username") String username, @Param("orderBy") String orderBy, @Param("sequence") String sequence, @Param("status") Integer status);

    @Select("select id, name, class_name, possibility, stage, consultant, " +
            "substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time," +
            "substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments from " +
            "(select distinct t1.tag as possibility, t9.value as stage, t8.nickname as consultant, t1.id, t1.name, t5.value as class_name,t6.create_time as time, t6.content as comments from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "left join s_customer_lesson t4 on t4.customer_id = t1.id " +
            "left join s_list_of_value t5 on t4.lesson_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_user_customer t7 on t1.id = t7.customer_id " +
            "left join s_user t8 on t8.id = t7.user_id " +
            "left join s_list_of_value t9 on t1.stage_id = t9.id " +
            "where t1.status = ${status} and t2.roleplay = 'teacher' and t7.roleplay = 'consultant' and t3.username = #{username} order by t6.create_time desc) tb " +
            "group by id, name, class_name, possibility, stage, consultant order by ${orderBy} ${sequence}")
    List<PoStudentModelStatus> getMyPoStudentsModel(@Param("username") String username, @Param("orderBy") String orderBy, @Param("sequence") String sequence, @Param("status") Integer status);
}